package com.dduggs.utils;

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.*;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.stereotype.Component;
import org.springframework.util.CollectionUtils;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.ResponseBody;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.OutputStream;
import java.io.UnsupportedEncodingException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

/**
 * excel 工具类
 *
 */
@Component
@Qualifier(value = "excelUtilsService")
public class ExcelUtilsService {

	/**
	 * 创建写入数据的Excel文件
	 *
	 * @param headers
	 * 		Excel 列头名称
	 * @param dataContent
	 * 		Excel 列数据
	 * @param headerStyle
	 * 		Excel列头样式
	 * @param dataContentStyle
	 * 		Excel列数据样式
	 *
	 * @return 返回创建的Excel文件
	 */
	public XSSFWorkbook createAndAddContent(String[] headers, List<List<String>> dataContent, XSSFCellStyle headerStyle,
	                                        XSSFCellStyle dataContentStyle){
		// 声明一个工作薄
		XSSFWorkbook workbook = new XSSFWorkbook();
		// 生成一个表格
		XSSFSheet sheet = workbook.createSheet("Sheet1");
		// 设置表格默认列宽度为15个字节
		sheet.setDefaultColumnWidth((short) 15);
		// 产生表格标题行
		XSSFRow row = sheet.createRow(0);
		for (short i = 0; i < headers.length; i++){
			XSSFCell cell = row.createCell(i);
			if (headerStyle != null){
				cell.setCellStyle(headerStyle);
			}
			cell.setCellValue(headers[i]);
		}
		// 遍历集合数据，产生数据行
		for (int i = 0; i < dataContent.size(); i++){
			row = sheet.createRow(i + 1);
			List<String> cells = dataContent.get(i);
			for (int j = 0; j < cells.size(); j++){
				XSSFCell xssfCell = row.createCell(j);
				if (dataContentStyle != null){
					xssfCell.setCellStyle(dataContentStyle);
				}
				xssfCell.setCellValue(cells.get(j));
			}
		}

		return workbook;
	}

	private int createOrderNoHeader(XSSFWorkbook workbook, XSSFSheet sheet, String orderNo, int startRow){

		//字体样式
		Font ztFont = workbook.createFont();
		ztFont.setFontName("微软雅黑 Light");
		ztFont.setFontHeightInPoints((short) 8);

		//单元格字体样式
		XSSFCellStyle fontStyle = workbook.createCellStyle();
		fontStyle.setFont(ztFont);

		//单元格垂直居中
		XSSFCellStyle orderNoStyle = workbook.createCellStyle();
		orderNoStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
		orderNoStyle.setFont(ztFont);

		//设置1-6列宽度
		sheet.setColumnWidth(0, (short) 19 * 256);
		sheet.setColumnWidth(1, (short) 28 * 256);
		sheet.setColumnWidth(2, (short) 6 * 256);
		sheet.setColumnWidth(3, (short) 11.55 * 256);
		sheet.setColumnWidth(4, (short) 9.4 * 256);
		sheet.setColumnWidth(5, (short) 9.4 * 256);

		XSSFRow row;
		//空白行1
		sheet.createRow(startRow);
		//空白行2
		row = sheet.createRow(startRow+1);
		row.setHeight((short) 405);

		//订单编号3,4合并行
		row = sheet.createRow(startRow+2);

		//订单编号单元格
		XSSFCell orderNoCell = row.createCell(0);
		orderNoCell.setCellValue(orderNo);
		sheet.createRow(3).setHeight((short) 330);
		row.getSheet().addMergedRegion(new CellRangeAddress(startRow+2, startRow+3, 0, 3));
		orderNoCell.setCellStyle(orderNoStyle); //字体

		//日期单元格
		XSSFCell         dateCell = row.createCell(4);
		SimpleDateFormat sdf      = new SimpleDateFormat("yyyy.MM.dd");
		dateCell.setCellValue("            " + sdf.format(new Date()));
		row.getSheet().addMergedRegion(new CellRangeAddress(startRow+2, startRow+3, 4, 5));
		dateCell.setCellStyle(orderNoStyle); //字体

		//订单编号后空白5,6,7行
		sheet.createRow(startRow+4);
		sheet.createRow(startRow+5);
		sheet.createRow(startRow+6);

		return sheet.getLastRowNum() + 1;
	}

	/**
	 * 创建单元格
	 *
	 * @param row
	 * 		第几行
	 * @param columnIndex
	 * 		第几列
	 * @param cellValue
	 * 		单元格值
	 * @param xssfCellStyle
	 * 		单元格样式
	 * @param firstRow
	 * 		合并单元格-开始行
	 * @param lastRow
	 * 		合并单元格-结束行
	 * @param firstCol
	 * 		合并单元格-开始列
	 * @param lastCol
	 * 		合并单元格-结束列
	 */
	private void createCell(XSSFRow row, int columnIndex, String cellValue, XSSFCellStyle xssfCellStyle, int firstRow, int lastRow, int firstCol,
	                        int lastCol){

		XSSFCell xssfCell = row.createCell(columnIndex);
		xssfCell.setCellValue(cellValue);
		if (firstRow >= 0 && lastRow >= 0 && firstCol >= 0 && lastCol >= 0){
			CellRangeAddress cellRangeAddress = new CellRangeAddress(firstRow, lastRow, firstCol, lastCol);
			row.getSheet().addMergedRegion(cellRangeAddress);
			this.setRegionStyle(xssfCellStyle, cellRangeAddress, row.getSheet());
		} else{
			xssfCell.setCellStyle(xssfCellStyle);
		}
	}

	/**
	 * 设置合并单元格外边框格式
	 *
	 * @param cs
	 * 		外边框格式
	 * @param region
	 * 		合并类
	 * @param sheet
	 * 		表格
	 */
	private void setRegionStyle(CellStyle cs, CellRangeAddress region, Sheet sheet){

		for (int i = region.getFirstRow(); i <= region.getLastRow(); i++){
			Row row = sheet.getRow(i);
			if (row == null){
				row = sheet.createRow(i);
			}
			for (int j = region.getFirstColumn(); j <= region.getLastColumn(); j++){
				Cell cell = row.getCell(j);
				if (cell == null){
					cell = row.createCell(j);
					cell.setCellValue("");
				}
				cell.setCellStyle(cs);
			}
		}
	}

	/**
	 * 格式化每行空的单元格
	 *
	 * @param row
	 * 		行
	 * @param xssfCellStyle
	 * 		单元格样式
	 */
	private void setEmptyCellStyle(XSSFRow row, XSSFCellStyle xssfCellStyle){

		for (int i = 0; i < 52; i++){
			XSSFCell xssfCell = row.getCell(i);
			if (xssfCell == null){
				row.createCell(i).setCellStyle(xssfCellStyle);
			}
		}
	}

//	//导出Excel
//	@RequestMapping(value = "admin/blogs/exportExcel", method = RequestMethod.POST)
//	@ResponseBody
//	public Object exceptionExportExcel(HttpServletRequest request, HttpServletResponse response) throws UnsupportedEncodingException {
//
//		ResponseDTO responseDTO = new ResponseDTO();
//		List<BlogEntity> blogList = blogRepository.findAll();
//		if (CollectionUtils.isEmpty(blogList)) {
//			responseDTO.setError(new Error(1000, "没有查询到需要导出的数据"));
//			return responseDTO;
//		}
//
//		String[] headers = new String[]{"序号","ID", "标题", "内容", "发布时间"};
//		List<List<String>> listDataContent = new ArrayList<>();
//		int index = 0;
//		for (BlogEntity entity : blogList) {
//			index++;
//			List<String> dataContent = new ArrayList<>(headers.length);
//			dataContent.add(String.valueOf(index));
//			dataContent.add(String.valueOf(entity.getId()));
//			dataContent.add(entity.getTitle());
//			dataContent.add(entity.getContent());
//			dataContent.add(StringUtils.formatDate(entity.getPubDate(), "yyyy-MM-dd"));
//			listDataContent.add(dataContent);
//		}
//
//		XSSFWorkbook workbook = excelUtilsService.createAndAddContent(headers, listDataContent, null, null);
//		String fileName = String.format("博客导出-%s.xlsx", StringUtils.formatDate(new Date(), "yyyyMMddHHmmsss"));
//		// 设置输出的格式
//		response.setCharacterEncoding("UTF-8");
//		response.setContentType("application/vnd.ms-excel;charset=UTF-8");
//		response.addHeader("Content-Disposition", "attachment;filename=" + new String(fileName.getBytes("gb2312"), "ISO8859-1"));
//		OutputStream oStream = null;
//		try {
//			oStream = response.getOutputStream();
//			workbook.write(oStream);
//			oStream.flush();
//		} catch (Exception exception) {
//			responseDTO.setError(new Error(1000, exception.getMessage()));
//		} finally {
//			try {
//				if (oStream != null) {
//					oStream.close();
//				}
//			} catch (IOException ioException) {
//			}
//		}
//		return null;
//	}
}
